﻿/* 
 * 
 *    Project           A report class 
 *    Module Name       Excel Report 
 *    Author            Justin.Gu 
 *    Creation Date     [12/10/2016]      
 *     
 */
using System;
using System.Data;
using System.Drawing;
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Drawing.Chart;
using OfficeOpenXml.Style;
using System.Collections;
using System.Collections.Generic;
using SPCLib;
using System.Configuration;
using System.Text.RegularExpressions;
using System.Xml;
using DTC.Model;
namespace common
{
    public class recordsaver
    {

        public class FailRateStatistic
        {
            public int FailCount { get; set; }
            public int TotalCount { get; set; }
            public int PassCount { get { return TotalCount - FailCount; } }
            public double FailRate { get { if (TotalCount > 0) return FailCount * 1.0 / TotalCount; else return 0; } }
        }
        private static string strBeginTime = "";
        private static string strEndTime="";
        private static List<CPKData> GetCPKDatas(DataTable dt)
        {
            List<CPKData> cpkDatas = new List<CPKData>();
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (dt.Columns[i].ColumnName.Contains("VALUE1"))
                {
                    double value;
                    string ColName = dt.Columns[i].ColumnName;
                    string strValue = "";
                    //找出VALUE1第一个不是空的值
                    for (int iRow = 0; iRow < dt.Rows.Count; iRow++)
                    {
                        string temp = dt.Rows[iRow][ColName].ToString();
                        if (!string.IsNullOrEmpty(temp))
                        {
                            strValue = temp;
                            break;
                        }
                    }
                    //如果是浮点数
                    if (double.TryParse(strValue, out value))
                    {
                        CPKData data = new CPKData();
                        string Name = "";
                        string Min = "";
                        string Max = "";
                        //找出记录中的测试项目，最大值，最小值。
                        for (int iRow = 0; iRow < dt.Rows.Count; iRow++)
                        {
                            string temp = dt.Rows[iRow][i - 3].ToString();
                            if (!string.IsNullOrEmpty(temp))
                            {
                                Name = dt.Rows[iRow][i - 3].ToString();
                                Min = dt.Rows[iRow][i - 2].ToString();
                                Max = dt.Rows[iRow][i - 1].ToString();
                                break;
                            }
                        }
                        //把VALue1里面的数据保存到List中
                        List<double> values = new List<double>();
                        foreach (DataRow row in dt.Rows)
                        {
                            double val;
                            string strVal = row[ColName].ToString();
                            if (double.TryParse(strVal, out val))
                            {
                                values.Add(val);
                            }
                        }
                        Common.ReportConfig rc = new Common.ReportConfig();
                        if (rc.isConfigFileExist()
                            &&rc.isModelExist(ConfigurationManager.AppSettings["CurrentModel"])
                            && rc.isItemExist(Name))
                        {
                            var rci = rc.getReportItembyName(Name);
                            data.LSL = rci.LSL;
                            data.USL = rci.USL;
                            data.MarkVals = rci.MarkVals;
                            data.UseMarkVals=  rci.UseMarkVal;
                        }
                        else
                        {
                            double tmp_val;
                            double.TryParse(Min, out tmp_val);
                            data.LSL = tmp_val;
                            double.TryParse(Max, out tmp_val);
                            data.USL = tmp_val;
                        }
                        data.Datas = values;
                        data.ItemName = Name;
                        cpkDatas.Add(data);
                    }
                }
            }
            return cpkDatas;
        }
        public static List<string> getRESULTCol(DataTable dt)
        {
            List<string> colNames = new List<string>();
            foreach (DataColumn cor in dt.Columns)
            {
                if (cor.ColumnName.ToUpper().Contains("RESULT"))
                {
                    colNames.Add(cor.ColumnName);
                }
            }
            return colNames;
        }
        public static void SaveFailRateStaticXml(Dictionary<string,FailRateStatistic> dic,string beginTime,string endTime)
        {
            if (!File.Exists("FailRateStatic.xml"))
            {
                Common.XMLHelper.CreateXmlDocument("FailRateStatic.xml", "Statistics", "1.0", "UTF-8", null);
            }
            XmlDocument doc = new XmlDocument();
            doc.Load("FailRateStatic.xml");
            XmlNode root = doc.SelectSingleNode("Statistics");
            XmlElement xe = doc.CreateElement(string.Format("T{0}-T{1}", beginTime.Replace(" ", "").Replace(":", "").Replace("-", ""), endTime.Replace(" ", "").Replace(":", "").Replace("-", "")));
            foreach (var kv in dic)
            {
                XmlElement xe1 = doc.CreateElement(kv.Key);
           //     xe1.InnerText=kv.Key;
                XmlElement xe2 = doc.CreateElement("PassQty");
                xe2.InnerText = kv.Value.PassCount.ToString();
                XmlElement xe3 = doc.CreateElement("FailQty");
                xe3.InnerText = kv.Value.FailCount.ToString();
                XmlElement xe4 = doc.CreateElement("TotalQty");
                xe4.InnerText = kv.Value.TotalCount.ToString();
                XmlElement xe5 = doc.CreateElement("FailRate");
                xe5.InnerText = kv.Value.FailRate.ToString("P");
                xe.AppendChild(xe1);
                xe1.AppendChild(xe2);
                xe1.AppendChild(xe3);
                xe1.AppendChild(xe4);
                xe1.AppendChild(xe5);              
            }
            root.AppendChild(xe);
            doc.Save("FailRateStatic.xml");
        }

        public static Dictionary<string, FailRateStatistic> GetFailRate(DataTable dt)
        {
            Dictionary<string, FailRateStatistic> dic = new Dictionary<string, FailRateStatistic>();
            List<string> colNames = getRESULTCol(dt);
            string[] items = ConfigurationManager.AppSettings["QueryItems"].Split(new char[] { ',' });
            dic.Add("SUMMARY", new FailRateStatistic() { FailCount = 0, TotalCount = 0 });
            foreach (var item in items)
            {
                FailRateStatistic frs = new FailRateStatistic() { FailCount = 0, TotalCount = 0 };
                dic.Add(item, frs);
            }
            foreach (DataRow row in dt.Rows)
            {
                bool bPass = true;
                foreach (string name in colNames)
                {
                    string tempName = row[name].ToString();
                    string tempName1 = name;
                    string ItemName = row[tempName1.Replace("RESULT", "NAME")].ToString();
                    if (!string.IsNullOrEmpty(tempName))
                    {
                        dic[ItemName].TotalCount++;
                    }
                    if (tempName.ToUpper().Contains("FAIL"))
                    {
                        dic[ItemName].FailCount++;
                        
                        bPass = false;
                    }
                }
                if (!bPass)
                {
                    dic["SUMMARY"].FailCount++;
                }
                dic["SUMMARY"].TotalCount++;
            }
            return dic;
        }
        public static void saveexcel(DataTable dt, string BeginTime, string EndTime)
        {
           
            string fileName = "ExcelReport-" + DateTime.Now.ToString("yyyy_MM_dd_HHmmss") + ".xlsx";
            string Dir = "D:\\ExcelReport";
            strBeginTime = BeginTime;
            strEndTime = EndTime;
            if (!Directory.Exists(Dir))
            {
                Directory.CreateDirectory("D:\\ExcelReport");
            }
            FileInfo file = new FileInfo(Dir + "\\" + fileName);
            using (ExcelPackage package = new ExcelPackage(file))
            {

                ExcelWorksheet ReportSheet = package.Workbook.Worksheets.Add("Report");
                ReportSheet.View.ShowGridLines = false;
                //    ReportSheet.Cells[1,1].Value = string.Format("{0} Test Report ({1}-{2})", ConfigurationManager.AppSettings["CurrentModel"], BeginTime, EndTime);
                SetReportTitle(BeginTime, EndTime, ReportSheet);
                int irow = 3;
                irow = SaveFailRateStatistics(dt, ReportSheet, irow);
                irow++;
                irow = SaveDetailStatistics(dt, ReportSheet, irow);
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Data");
                SaveDatas(dt, worksheet);
                package.Save(); //保存文件   

            }
        }

        private static void SetReportTitle(string BeginTime, string EndTime, ExcelWorksheet ReportSheet)
        {
            ExcelRange range = ReportSheet.Cells[1, 1, 1, 12];
            range.Style.Font.Bold = true;
            range.Style.Font.Size = 16F;
            range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            range.Style.Font.Name = "Microsoft Sans Serif";
            range.Merge = true;
            range.Value = string.Format("{0} Test Report", ConfigurationManager.AppSettings["CurrentModel"]);
            ExcelRange range1 = ReportSheet.Cells[2, 1, 2, 12];
            range1.Merge = true;
            range1.Value = string.Format("{0}~{1}", BeginTime, EndTime);
            range1.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
        }

        private static void SaveDatas(DataTable dt, ExcelWorksheet worksheet)
        {
            DataTable dataTest = dt;
            RemoveSomeCorlumns(dataTest);

            //设置表头
            for (int col = 1; col <= dataTest.Columns.Count; col++)
            {
                worksheet.Cells[1, col].Value = dataTest.Columns[col - 1].ColumnName;
            }
            //设置数据  

            for (int row = 1; row <= dataTest.Rows.Count; row++)
            {

                for (int col = 1; col <= dataTest.Columns.Count; col++)
                {
                    string strValue = dataTest.Rows[row - 1][col - 1].ToString();
                    worksheet.Cells[row + 1, col].Value = strValue;
                }
            }
        }

        private static void RemoveSomeCorlumns(DataTable dataTest)
        {
            List<string> corRemoveList = new List<string>();
            for (int i = 0; i < dataTest.Columns.Count; i++)
            {
                string corName = dataTest.Columns[i].ColumnName;
                if (corName.Contains("ID") || (corName.Contains("VALUE2") || Regex.IsMatch(corName, @"MODEL\d")))
                {
                    corRemoveList.Add(corName);
                }
                if (corName.Contains("MIN"))
                {
                    string val = "";
                    foreach (DataRow row in dataTest.Rows)
                    {
                        val = row[corName].ToString();
                        if (!string.IsNullOrEmpty(val))
                        {
                            break;
                        }
                    }
                    if (string.IsNullOrEmpty(val) || val.Contains("NA"))
                    {
                        corRemoveList.Add(corName);
                        string strMAX = Regex.Replace(corName, "MIN", "MAX");
                        corRemoveList.Add(strMAX);
                        string strVALUE = Regex.Replace(corName, "MIN", "VALUE1");
                        corRemoveList.Add(strVALUE);
                    }
                }
            }
            foreach (string name in corRemoveList)
            {
                dataTest.Columns.Remove(name);
            }
            dataTest.AcceptChanges();
        }

        private static int SaveDetailStatistics(DataTable dt, ExcelWorksheet ReportSheet, int irow)
        {
            int rowBegin = irow;
            List<CPKData> cpkDatas = GetCPKDatas(dt);
            List<ExcelChart> charts = new List<ExcelChart>();
            for (int idata = 0; idata < cpkDatas.Count; idata++)
            {
                int iRowBegin = irow;
                ReportSheet.Cells[irow, 1].Value = cpkDatas[idata].ItemName;
                irow++;
                Dictionary<string, double> dic = cpkDatas[idata].GetCPKDictionary();
                int rowCount = 0;
                foreach (var kp in dic)
                {
                    int row = irow;
                    int col = 1 + rowCount * 2;
                    ExcelSetValueAndStyle(ReportSheet.Cells[row, col], kp.Key);
                    ExcelSetValueAndStyle(ReportSheet.Cells[row, col + 1], kp.Value);
                    if (++rowCount > 5)
                    {
                        irow++;
                        rowCount = 0;
                    }
                }
                irow++;
                ReportSheet.Cells[irow, 1].Value = "Statistics by Groups:";
                irow++;
                List<GroupItem> gis = cpkDatas[idata].getGroupItems();
                //            ReportSheet.Cells[irow , 1].Value = "Item";
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 1], "Group");
                ExcelSetValueAndStyle(ReportSheet.Cells[irow + 1, 1], "Qty");
                ExcelSetValueAndStyle(ReportSheet.Cells[irow + 2, 1], "Percent");
                for (int i = 0; i < gis.Count; i++)
                {
                    ExcelSetValueAndStyle(ReportSheet.Cells[irow, 2+i], gis[i].GroupTitle);
                    ExcelSetValueAndStyle(ReportSheet.Cells[irow+1, 2 + i], gis[i].qty);
                    ReportSheet.Cells[irow + 2, 2 + i].Style.Numberformat.Format = @"0.00%";//设置为百分比显示
                    ExcelSetValueAndStyle(ReportSheet.Cells[irow + 2, 2 + i], gis[i].Percent);          
                }
                irow=irow + 3;
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 1], "System Fail");
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 2], gis[0].systemFail);
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 3], gis[0].systemFail*1.0/gis[0].total);
                ReportSheet.Cells[irow, 3].Style.Numberformat.Format = @"0.00%";
                ExcelChart bar = ReportSheet.Drawings.AddChart(cpkDatas[idata].ItemName, eChartType.ColumnClustered) ;
                var ser = bar.Series.Add(ReportSheet.Cells[irow - 1, 2, irow - 1, 12], ReportSheet.Cells[irow - 3, 2, irow - 3, 12]);
                ser.Header = "占比";
                bar.Legend.Position = eLegendPosition.Right;
                irow++;

                //     bar.Legend.Add();
                bar.Title.Text = cpkDatas[idata].ItemName + "数据分布"; //设置图表的名称   
                bar.SetPosition(iRowBegin, 0, 13, 0);
                bar.SetSize(410, 240); //设置图表大小            
                bar.ShowHiddenData = true;
                charts.Add(bar);
                irow++;
            }
            int j = 0;
            for (int i = 0; i < charts.Count; i++)
            {
                int c = i % 2;
                j++;
                charts[i].SetPosition(irow, 0, 6*c, 0);
                if (j > 1)
                {
                    irow = irow + 15;
                    j = 0;
                }
                
            }
                return irow;
        }
        private static void ExcelSetValueAndStyle(ExcelRange range, object Value)
        {
            range.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
            range.AutoFitColumns(10, 80);
            range.Value = Value;
        }

        private static int SaveFailRateStatistics(DataTable dt, ExcelWorksheet ReportSheet, int irow)
        {
            int rowBegin = irow;
            Dictionary<string, FailRateStatistic> dicFailRate = GetFailRate(dt);
            SaveFailRateStaticXml(dicFailRate, strBeginTime, strEndTime);
            ExcelSetValueAndStyle(ReportSheet.Cells[irow, 1], "Item");
            ExcelSetValueAndStyle(ReportSheet.Cells[irow, 2], "Fail Qty");
            ExcelSetValueAndStyle(ReportSheet.Cells[irow, 3], "Pass Qty");
            ExcelSetValueAndStyle(ReportSheet.Cells[irow, 4], "Total Qty");
            ExcelSetValueAndStyle(ReportSheet.Cells[irow, 5], "Fail Rate");
            irow++;
            foreach (var kv in dicFailRate)
            {
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 1], kv.Key);
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 2], kv.Value.FailCount);
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 3], kv.Value.PassCount);
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 4], kv.Value.TotalCount);
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 5], kv.Value.FailRate.ToString("P"));
                irow++;
            }
            return irow;
        }
    }
    public class CPKData
    {
        public string ItemName { get; set; }
        public double USL { get; set; }
        public double LSL { get; set; }
        public List<double> Datas { get; set; }
        public double[] MarkVals { get; set; }
        public bool UseMarkVals { get; set; }
        public List<GroupItem> getGroupItems()
        {
            List<GroupItem> gis = new List<GroupItem>();
            SPCLib.SPCFunctions spc = new SPCFunctions(Datas.ToArray(), USL, LSL);
            spc.UseMarkVals = UseMarkVals;
            spc.MarkVals = MarkVals;
            gis = spc.getGroupItems();
            return gis;
        }
        public Dictionary<string, double> GetCPKDictionary()
        {
            Dictionary<string, double> kvs = new Dictionary<string, double>();
            SPCLib.SPCFunctions spc = new SPCFunctions(Datas.ToArray(), USL, LSL);
            kvs.Add("CL", Math.Round(spc.getCL(), 3));
            kvs.Add("LCL", Math.Round(spc.getLCL(), 3));
            kvs.Add("UCL", Math.Round(spc.getUCL(), 3));
            kvs.Add("SL", Math.Round(spc.getSL(), 3));
            kvs.Add("LSL", Math.Round(spc.getLSL(), 3));
            kvs.Add("USL", Math.Round(spc.getUSL(), 3));
            kvs.Add("Max", Math.Round(spc.SPCMax(), 3));
            kvs.Add("Mean", Math.Round(spc.SPCMean(), 3));
            kvs.Add("Min", Math.Round(spc.SPCMin(), 3));
            kvs.Add("Range", Math.Round(spc.SPCRange(), 3));
            kvs.Add("CP", Math.Round(spc.SPCCp(), 3));
            kvs.Add("CPL", Math.Round(spc.SPCCpl(), 3));
            kvs.Add("CPU", Math.Round(spc.SPCCpm(), 3));
            kvs.Add("CPK", Math.Round(spc.SPCCpk(), 3));
            kvs.Add("StdDev", Math.Round(spc.SPCStdDev(), 3));
            return kvs;
        }
        public double[][] GetNormalArray()
        {
            SPCLib.SPCFunctions spc = new SPCFunctions(Datas.ToArray(), USL, LSL);
            return spc.NormalStepUpperLowerMidCountNormCumu();
        }
        public double[][] GetBarArray()
        {
            SPCLib.SPCFunctions spc = new SPCFunctions(Datas.ToArray(), USL, LSL);
            spc.setHistogramGroupNumber(7);
            return spc.HistogramStepUpperLowerMidCountNormCumu();
        }
    }
}